/*
PROGRAM BY: JEFFREY WANG
Last edited 05/14/2021

This program creates a concordance between product codes from CMF product trailersT and HS code from census

*/ 

%include 'yyyy/pdata.sas';

options obs=max;
    

libname ecroot 'xxxx/data';
%let input=xxxx/input ;
%let output=xxxx/data ;

%macro nmerge(l);
    %let li=%eval(&l+1) ;
    %let lo=%eval(&l-1)  ;
* sort to prepare for merge;
 proc sort data=hs_naics02;
    by naics&l;
 run;
 
  proc sort data=unmatched&li;
    by naics&l;
 run;

* collapse mnaics by naics for merge;
 proc means sum noprint data=unmatched&li;
   by naics&l ;
   var pv_total ;
   output out=naicspc_n&l(keep=naics&l pv_total) sum()= ;
 run;

    
* match by naics codes;
data prod_hs_naics_&l(drop=naics&lo) unmatched&l(keep=naics&lo pv_total);
  length naics&lo $ &lo ;
    merge naicspc_n&l(in=a) hs_naics02(in=b keep=naics&l hs naics_matchtype);
    by naics&l;
    if a=1;
    if b=1 then matched = 1;
    if b=1 then match_level = &l;
    naics&lo = substr(naics&l,1,&lo);
    if b=1 then output prod_hs_naics_&l;
    else output unmatched&l ;
run;
%mend;
        
%macro prod_hs_match(y);
* in 1992, use sic code instead of naics code;
%if &y. >= 1992 and &y. < 1997  %then %do;
 
data cmfprod&y.;
    set ecroot.cmf_prod_match1992;
run;
        
proc means data=cmfprod&y. NWAY NOPRINT;
    class curpc;
    var pv;
    output out=curpc_code sum = pv_total;
run;        

* merge normal codes with special codes;
* create a flag to indicate whether a code maps well with naics;
* naics_flag = C if complete, I if incomplete (less than NAICS6), and O if other (cant be matched at all);
data curpc_sic;
    set curpc_code;
    sic=substr(curpc,1,4) ;
    sic4=sic;
run;

proc import out=hs_sic datafile = "&input/hs_sic_naics_imports_89_117_20180927_old.dta" dbms=dta replace;
run;
    
* read data and only keep 2007 concordances;
* also extract 5,4,3-digit NAICS codes and their HS mappings;
data hs_sic02(keep = hs sic: sic_matchtype);
   length sic4 $ 4 sic3 $ 3  hs $ 10; 
    set hs_sic(rename=(commodity=hs10));
    sic4 = tranwrd(sic,'X','0');
    sic3=substr(sic4,1,3);
    hs2=hs10+10000000000;
    hs=substr(hs2,3,10);  * fill in the missing zeros;
    if year=&y.;
run;
       
*Merge by naics6 -n3 levels ;
data unmatched5;
  length naics6 $ 6 ;
  set curpc_sic (drop=sic4);
   sic4=sic;
run;    
     
* sort to prepare for merge; 
 proc sort data=hs_sic02;
    by sic4;
 run;
 
  proc sort data=unmatched5;
    by sic4;
 run;

* collapse curpc by sic for merge;
 proc means sum noprint data=unmatched5;
   by sic4 ;
   var pv_total ;
   output out=curpc_s4(keep=sic4 pv_total) sum()= ;
 run;

    
* match by naics codes;
data prod_hs_sic_4(drop=sic3) unmatched4(keep=sic3 pv_total);
  length sic3 $ 3 ;
    merge curpc_s4(in=a) hs_sic02(in=b keep=sic4 hs sic_matchtype);
    by sic4;
    if a=1;
    if b=1 then matched = 1;
    if b=1 then match_level = 4;
    sic3 = substr(sic4,1,3);
    if b=1 then output prod_hs_sic_4;
    else output unmatched4 ;
run;
        
* sort to prepare for merge;
 proc sort data=hs_sic02;
    by sic3;
 run;
 
  proc sort data=unmatched4;
    by sic3;
 run;

* collapse mnaics by sic for merge;
 proc means sum noprint data=unmatched4;
   by sic3 ;
   var pv_total ;
   output out=curpc_s3(keep=sic3 pv_total) sum()= ;
 run;

    
* match by naics codes;
data prod_hs_sic_3(drop=sic2) unmatched3(keep=sic2 pv_total);
    length sic2 $ 2 ;
    merge curpc_s3(in=a) hs_sic02(in=b keep=sic3 hs sic_matchtype);
    by sic3;
    if a=1;
    if b=1 then matched = 1;
    if b=1 then match_level = 3;
    sic2 = substr(sic3,1,2);
    if b=1 then output prod_hs_sic_3;
    else output unmatched3 ;
run;


*append all the datasets for mnaics codes that appear in data ;
data prod_hs_all ;
   set prod_hs_sic_4 prod_hs_sic_3;
   sic=sic4;
   if sic=" " & sic3~=" " then sic=sic3||"0" ;
run;        

*dataset with the mappings ;
data prod_code_bridge&y.;
    set prod_hs_all(keep=hs sic match_level);
    year = &y.;
run; 
%end;
        
%if &y.>=1997 %then %do;


%if &y.>=1997 and &y.<2002 %then %do;
data cmfprod&y.;
    set ecroot.cmf_prod_match1997;
run;
%end;

%if &y.>=2002 and &y.<2007 %then %do;
data cmfprod&y.;
    set ecroot.cmf_prod_match2002;
run;
%end;

%if &y.>=2007 and &y.<2012 %then %do;
data cmfprod&y.;
    set ecroot.cmf_prod_match2007;
run;
%end;

%if &y.>=2012 and &y.<2017 %then %do;
data cmfprod&y.;
    set ecroot.cmf_prod_match2012;
run;
%end;
    
* collapse to just keep all unique values of mnaics that;
* appear in cmfmat2007;
proc means data=cmfprod&y. NWAY NOPRINT;
    class naicspc;
    var pv;
    output out=naicspc_code sum = pv_total;
run;

* merge normal codes with special codes;
* create a flag to indicate whether a code maps well with naics;
* naics_flag = C if complete, I if incomplete (less than NAICS6), and O if other (cant be matched at all);
data naicspc_naics;
    set naicspc_code;
    naics=substr(naicspc,1,6) ;
    naics6=naics;
run;

proc sort data=naicspc_naics;
    by naics6;
run;


    
/*

Next match MNAICS with HS-10 using NAICS as a middle point
           
First match using naics6 which are supposed to be complete NAICS codes
Obs which are matched are done
Then match with NAICS-5,4,3
                                  
*/


* First import HS-NAICS concordances constructed by Pierce-Schott;
* interested in classifying imports so will only use import data;
proc import out=hs_naics datafile = "&input/hs_sic_naics_imports_89_117_20180927_old.dta" dbms=dta replace;
run;
    
* read data and only keep 2007 concordances;
* also extract 5,4,3-digit NAICS codes and their HS mappings;
data hs_naics02(keep = hs naics: naics_matchtype);
   length naics6 $ 6 naics5 $ 5 naics4 $ 4 naics3 $ 3  hs $ 10; 
    set hs_naics(rename=(commodity=hs10));
    naics6 = tranwrd(naics,'X','0');
    naics5=substr(naics6,1,5);
    naics4=substr(naics6,1,4);
    naics3=substr(naics6,1,3);
    hs2=hs10+10000000000;
    hs=substr(hs2,3,10);  * fill in the missing zeros;
    if year=&y.;
run;
    

*Merge by naics6 -n3 levels ;
data unmatched7;
  length naics6 $ 6 ;
  set naicspc_naics (drop=naics6);
   naics6=naics; *I fixed this so naics has partials and the mappings from special ; 
run;

* here match to all naics6 values that are consistent with partials below;
 
%nmerge(6);
%nmerge(5);
%nmerge(4);
%nmerge(3);

*append all the datasets for mnaics codes that appear in data ;
data prod_hs_all ;
   set prod_hs_naics_6 prod_hs_naics_5 prod_hs_naics_4 prod_hs_naics_3 ;
   naics=naics6;
   if naics=" " & naics5~=" " then naics=naics5||"0" ;
   if naics=" " & naics4~=" " then naics=naics4||"00" ;
   if naics=" " & naics3~=" " then naics=naics3||"000" ;
run;   

*Analyze share of mat that is matched and match levels;
  *note that pv_total is unique by naics;
  *total value of unmatched is in unmatched3;
  
proc sort data=prod_hs_all; by naics; run;

data match_stats;
  set prod_hs_all;
  by naics;
  if first.naics=1;
run;

data match_stats2(keep=naics pv_total match_level matched);
  set match_stats unmatched3(in=b);
  if b=1 then matched=0;
  if b=1 then match_level=0;
run;

proc sort data=match_stats2; by match_level ; run;

*collapse by match level ;
proc means sum noprint data=match_stats2;
   by match_level; 
   var pv_total ;
   output out=match_stats3(rename=(_type_=mvar)) sum()= ;
run;


*total material costs ;
proc means sum noprint data=match_stats2;  
  var pv_total;
  output out=match_tots(rename=(_type_=mvar) drop=_freq_) sum(pv_total)=tot_pv;
run;  

*dataset with the mappings ;
data prod_code_bridge&y.;
    set prod_hs_all(keep=hs naics match_level);
    year = &y.;
run; 
%end;
%mend;

*do this 2007;
%macro loop1();
    %do y=2007 %to 2007 %by 1;
        %prod_hs_match(&y.);
    %end;
%mend;
%loop1();
             
*output just 2007 ;
    data ecroot.prod_code_bridge_all;
        set prod_code_bridge2007;
    run;

  


/*
*do this for all years;
%macro loop1();
    %do y=1992 %to 2016 %by 1;
        %prod_hs_match(&y.);
    %end;
%mend;
%loop1();
        
        
*append everything together;
%macro loop2();
    data ecroot.prod_code_bridge_all;
        set prod_code_bridge1992;
    run;

    %do y=1993 %to 2016 %by 1;
        data ecroot.prod_code_bridge_all;
            set ecroot.prod_code_bridge_all prod_code_bridge&y.;
        run;
    %end;
%mend;
%loop2();
*/
 
